Data Refrsh

Load libraries

library(modeltime)
library(dplyr)
library(EIAapi)
library(jsonlite)
library(gt)
library(plotly)
library(lubridate)
library(modeltime)
source("../pipeline/eia_data.R")
source("../pipeline/backtesting.R")

API Settings:

meta_json <- read_json(path = "../settings/settings.json")
s <- meta_json$series
series <- lapply(1:length(s), function(i) {
    return(data.frame(
        parent_id = s[[i]]$parent_id,
        parent_name = s[[i]]$parent_name,
        subba_id = s[[i]]$subba_id,
        subba_name = s[[i]]$subba_name
    ))
}) |>
    bind_rows()


facets_template <- list(
    parent = NULL,
    subba = NULL
)

eia_api_key <- Sys.getenv("EIA_API_KEY")

api_path <- meta_json$api_path
meta_path <- meta_json$meta_path
data_path <- meta_json$data_path
forecast_path <- meta_json$forecast_path
forecast_log_path <- meta_json$forecast_log_path
calibrated_models_path <- meta_json$calibrated_models_path

h <- meta_json$backtesting$h
lags <- meta_json$backtesting$features$lags |> unlist()
train_length <- meta_json$train_length
offset <- meta_json$offset
tz <- meta_json$timezone
meta_obj <- get_metadata(api_key = eia_api_key, api_path = api_path, meta_path = meta_path, series = series)

gt(meta_obj$request_meta)
parent subba end_act request_start end updates_available
CISO PGAE 2024-07-12 07:00:00 2024-07-12 08:00:00 2024-07-12 07:00:00 FALSE
CISO SCE 2024-07-12 07:00:00 2024-07-12 08:00:00 2024-07-12 07:00:00 FALSE
CISO SDGE 2024-07-12 07:00:00 2024-07-12 08:00:00 2024-07-12 07:00:00 FALSE
CISO VEA 2024-07-12 07:00:00 2024-07-12 08:00:00 2024-07-12 07:00:00 FALSE 
m <- meta_obj$request_meta
index <- meta_obj$last_index + 1

data <- NULL
meta_new <- NULL

for (i in 1:nrow(m)) {
    facets <- facets_template
    facets$parent <- m$parent[i]
    facets$subba <- m$subba[i]
    start <- m$request_start[i]
    end <- m$end[i]
    print(paste(facets$parent, facets$subba, sep = " - "))

    if (m$updates_available[i]) {
        temp <- eia_backfill(
            start = start - lubridate::hours(24),
            end = end + lubridate::hours(24),
            offset = offset,
            api_key = eia_api_key,
            api_path = paste(api_path, "data", sep = ""),
            facets = facets
        ) |> dplyr::filter(time >= start & time <= end)

        index <- seq.POSIXt(from = start, to = end, by = "hour")
        ts_obj <- data.frame(period = index) |>
            left_join(temp, by = c("period" = "time"))
    } else {
        ts_obj <- NULL
        print("No new data is available")
    }

    meta_temp <- create_metadata(data = ts_obj, start = start, end = end, type = "refresh")

    if (is.null(ts_obj)) {
        meta_temp$parent <- m$parent[i]
        meta_temp$subba <- m$subba[i]
    }

    if (meta_temp$success) {
        print("Append the new data")
        d <- append_data(data_path = data_path, new_data = ts_obj, save = TRUE)
        meta_temp$update <- TRUE
    } else {
        meta_temp$update <- FALSE
        meta_temp$comments <- paste(meta_temp$comments, "The data refresh failed, please check the log; ", sep = "")
    }
    meta_temp$index <- NA
    meta_df <- as.data.frame(meta_temp)
    if (!is.null(ts_obj)) {
        data <- bind_rows(data, ts_obj)
    }
    meta_new <- bind_rows(meta_new, meta_df)
}
[1] "CISO - PGAE"
[1] "No new data is available"
[1] "CISO - SCE"
[1] "No new data is available"
[1] "CISO - SDGE"
[1] "No new data is available"
[1] "CISO - VEA"
[1] "No new data is available"
gt(meta_new)
index parent subba time start end start_act end_act start_match end_match n_obs na type update success comments
NA CISO PGAE 2024-07-12 21:12:17.983872 2024-07-12 08:00:00 2024-07-12 07:00:00 NA NA NA NA NA NA refresh FALSE FALSE No new data is available; The data refresh failed, please check the log;
NA CISO SCE 2024-07-12 21:12:17.986211 2024-07-12 08:00:00 2024-07-12 07:00:00 NA NA NA NA NA NA refresh FALSE FALSE No new data is available; The data refresh failed, please check the log;
NA CISO SDGE 2024-07-12 21:12:17.987833 2024-07-12 08:00:00 2024-07-12 07:00:00 NA NA NA NA NA NA refresh FALSE FALSE No new data is available; The data refresh failed, please check the log;
NA CISO VEA 2024-07-12 21:12:17.98925 2024-07-12 08:00:00 2024-07-12 07:00:00 NA NA NA NA NA NA refresh FALSE FALSE No new data is available; The data refresh failed, please check the log; 
meta_updated <- append_metadata(meta_path = meta_path, new_meta = meta_new, save = TRUE, init = FALSE)
[1] "Saving the metadata file"

Plot the Series

We will use Plotly to visualize the series:

if (!is.null(data)) {
    d <- data |> arrange(subba, period)

    p <- plot_ly(d, x = ~period, y = ~value, color = ~subba, type = "scatter", mode = "lines")

    p
} else {
    print("No new data is available")
}
[1] "No new data is available"
data <- readr::read_csv(file = data_path, col_types = readr::cols(
    period = readr::col_datetime(format = ""),
    subba = readr::col_character(),
    subba_name = readr::col_character(),
    parent = readr::col_character(),
    parent_name = readr::col_character(),
    value = readr::col_double(),
    value_units = readr::col_character()
))

head(data)
# A tibble: 6 x 8
  period              subba subba_name      parent parent_name value value_units
  <dttm>              <chr> <chr>           <chr>  <chr>       <dbl> <chr>      
1 2018-07-01 08:00:00 PGAE  Pacific Gas an~ CISO   California~ 12522 megawattho~
2 2018-07-01 09:00:00 PGAE  Pacific Gas an~ CISO   California~ 11745 megawattho~
3 2018-07-01 10:00:00 PGAE  Pacific Gas an~ CISO   California~ 11200 megawattho~
4 2018-07-01 11:00:00 PGAE  Pacific Gas an~ CISO   California~ 10822 megawattho~
5 2018-07-01 12:00:00 PGAE  Pacific Gas an~ CISO   California~ 10644 megawattho~
6 2018-07-01 13:00:00 PGAE  Pacific Gas an~ CISO   California~ 10559 megawattho~
# i 1 more variable: type <chr>
p <- plot_ly(data, x = ~period, y = ~value, color = ~subba, type = "scatter", mode = "lines")

p

Refresh the forecast

fc <- NULL
fc <- refresh_forecast(
    input = data,
    forecast_log_path = forecast_log_path,
    forecast_path = forecast_path,
    calibrated_models_path = calibrated_models_path,
    h = h,
    index = "period",
    var = "value",
    train_length = 24 * 31 * 25,
    lags = lags,
    init = FALSE,
    save = TRUE,
    seasonal = TRUE,
    trend = TRUE
)
New data is avaiable, starting the forecast refresh process
Add lag: 24
Add lag: 25
Add lag: 26
Add lag: 27
Add lag: 28
Add lag: 48
Add lag: 72
Add lag: 8760
Add lag: 24
Add lag: 25
Add lag: 26
Add lag: 27
Add lag: 28
Add lag: 48
Add lag: 72
Add lag: 8760
Add lag: 24
Add lag: 25
Add lag: 26
Add lag: 27
Add lag: 28
Add lag: 48
Add lag: 72
Add lag: 8760
Add lag: 24
Add lag: 25
Add lag: 26
Add lag: 27
Add lag: 28
Add lag: 48
Add lag: 72
Add lag: 8760
if (!is.null(fc)) {
    head(fc)
    plot_forecast(
        input = data,
        forecast = fc,
        var = "value",
        index = "period",
        hours = 24 * 3
    )
}

Score the Forecast

fc_log <- load_forecast_log(forecast_log_path = forecast_log_path)

score_rows <- which(!fc_log$score)


if (length(score_rows) == 0) {
    message("All models were scored ")
} else {
    subba <- unique(fc_log$subba[score_rows])

    fc <- load_forecast(forecast_path = forecast_path)

    for (i in subba) {
        print(i)
        d <- data |> dplyr::filter(subba == i)

        r <- which(fc_log$subba == i & !fc_log$score)

        for (l in r) {
            f <- fc |>
                dplyr::filter(forecast_label == fc_log$forecast_label[l], subba == i) |>
                dplyr::left_join(d |> dplyr::select(time = period, subba, value), by = c("time", "subba")) |>
                dplyr::filter(!is.na(value))

            fc_log$mape[l] <- mean(abs(f$value - f$yhat) / f$value)

            fc_log$rmse[l] <- (mean((f$value - f$yhat)^2))^0.5

            fc_log$coverage[l] <- length(which(f$value <= f$upper & f$value >= f$lower)) / nrow(f)

            if (nrow(f) == fc_log$h[l]) {
                fc_log$score[l] <- TRUE
            }

            write.csv(fc_log, forecast_log_path, row.names = FALSE)
        }
    }

    gt::gt(fc_log[score_rows, ])
}
[1] "PGAE"
[1] "SCE"
[1] "SDGE"
[1] "VEA"
index subba model method time forecast_label start end h n_obs n_obs_flag na_flag success score mape rmse coverage
5 PGAE LM model6 2024-07-12 06:25:29.05864 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 24 TRUE FALSE TRUE FALSE 0.08850847 1732.46074 0.75
6 SCE GLMNET model18 2024-07-12 06:25:29.060925 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 24 TRUE FALSE TRUE FALSE 0.05054926 929.83153 1.00
7 SDGE LM model7 2024-07-12 06:25:29.063636 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 24 TRUE FALSE TRUE FALSE 0.04453518 114.17980 1.00
8 VEA LM model5 2024-07-12 06:25:29.066704 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 24 TRUE FALSE TRUE FALSE 0.10235516 20.67417 0.75
9 PGAE LM model6 2024-07-12 07:12:18.325325 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 24 TRUE FALSE TRUE FALSE 0.08850847 1732.46074 0.75
10 SCE GLMNET model18 2024-07-12 07:12:18.328228 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 24 TRUE FALSE TRUE FALSE 0.05054926 929.83153 1.00
11 SDGE LM model7 2024-07-12 07:12:18.331235 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 24 TRUE FALSE TRUE FALSE 0.04453518 114.17980 1.00
12 VEA LM model5 2024-07-12 07:12:18.334265 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 24 TRUE FALSE TRUE FALSE 0.10235516 20.67417 0.75
13 PGAE LM model6 2024-07-12 08:15:38.25728 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.08850847 1732.46074 0.75
14 SCE GLMNET model18 2024-07-12 08:15:38.259486 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.05054926 929.83153 1.00
15 SDGE LM model7 2024-07-12 08:15:38.261747 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.04453518 114.17980 1.00
16 VEA LM model5 2024-07-12 08:15:38.264093 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.10235516 20.67417 0.75
17 PGAE LM model6 2024-07-12 09:13:19.498404 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.08850847 1732.46074 0.75
18 SCE GLMNET model18 2024-07-12 09:13:19.500615 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.05054926 929.83153 1.00
19 SDGE LM model7 2024-07-12 09:13:19.50291 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.04453518 114.17980 1.00
20 VEA LM model5 2024-07-12 09:13:19.505205 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.10235516 20.67417 0.75
21 PGAE LM model6 2024-07-12 10:12:32.835269 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.08850847 1732.46074 0.75
22 SCE GLMNET model18 2024-07-12 10:12:32.837656 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.05054926 929.83153 1.00
23 SDGE LM model7 2024-07-12 10:12:32.839982 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.04453518 114.17980 1.00
24 VEA LM model5 2024-07-12 10:12:32.842395 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.10235516 20.67417 0.75
25 PGAE LM model6 2024-07-12 11:10:23.294598 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.08850847 1732.46074 0.75
26 SCE GLMNET model18 2024-07-12 11:10:23.296758 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.05054926 929.83153 1.00
27 SDGE LM model7 2024-07-12 11:10:23.299045 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.04453518 114.17980 1.00
28 VEA LM model5 2024-07-12 11:10:23.301297 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.10235516 20.67417 0.75
29 PGAE LM model6 2024-07-12 12:21:04.942089 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.08850847 1732.46074 0.75
30 SCE GLMNET model18 2024-07-12 12:21:04.944458 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.05054926 929.83153 1.00
31 SDGE LM model7 2024-07-12 12:21:04.946802 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.04453518 114.17980 1.00
32 VEA LM model5 2024-07-12 12:21:04.949097 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.10235516 20.67417 0.75
33 PGAE LM model6 2024-07-12 13:15:02.380661 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.08850847 1732.46074 0.75
34 SCE GLMNET model18 2024-07-12 13:15:02.382852 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.05054926 929.83153 1.00
35 SDGE LM model7 2024-07-12 13:15:02.385175 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.04453518 114.17980 1.00
36 VEA LM model5 2024-07-12 13:15:02.387495 2024-07-11 2024-07-11 2024-07-11 23:00:00 24 48 FALSE FALSE FALSE FALSE 0.10235516 20.67417 0.75
37 PGAE LM model6 2024-07-12 14:11:09.113551 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
38 SCE GLMNET model18 2024-07-12 14:11:09.115793 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
39 SDGE LM model7 2024-07-12 14:11:09.118101 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
40 VEA LM model5 2024-07-12 14:11:09.120441 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
41 PGAE LM model6 2024-07-12 15:12:04.435242 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
42 SCE GLMNET model18 2024-07-12 15:12:04.437444 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
43 SDGE LM model7 2024-07-12 15:12:04.439684 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
44 VEA LM model5 2024-07-12 15:12:04.441964 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
45 PGAE LM model6 2024-07-12 16:14:28.475842 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
46 SCE GLMNET model18 2024-07-12 16:14:28.47806 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
47 SDGE LM model7 2024-07-12 16:14:28.480315 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
48 VEA LM model5 2024-07-12 16:14:28.482589 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
49 PGAE LM model6 2024-07-12 17:12:17.801791 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
50 SCE GLMNET model18 2024-07-12 17:12:17.804001 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
51 SDGE LM model7 2024-07-12 17:12:17.806316 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
52 VEA LM model5 2024-07-12 17:12:17.80864 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
53 PGAE LM model6 2024-07-12 18:14:43.513978 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
54 SCE GLMNET model18 2024-07-12 18:14:43.516204 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
55 SDGE LM model7 2024-07-12 18:14:43.518458 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
56 VEA LM model5 2024-07-12 18:14:43.520778 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
57 PGAE LM model6 2024-07-12 19:09:44.795551 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
58 SCE GLMNET model18 2024-07-12 19:09:44.797971 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
59 SDGE LM model7 2024-07-12 19:09:44.800349 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
60 VEA LM model5 2024-07-12 19:09:44.802675 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
61 PGAE LM model6 2024-07-12 20:12:25.315747 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
62 SCE GLMNET model18 2024-07-12 20:12:25.318023 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
63 SDGE LM model7 2024-07-12 20:12:25.320324 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
64 VEA LM model5 2024-07-12 20:12:25.322639 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
65 PGAE LM model6 2024-07-12 21:12:23.443145 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
66 SCE GLMNET model18 2024-07-12 21:12:23.445463 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
67 SDGE LM model7 2024-07-12 21:12:23.447785 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN
68 VEA LM model5 2024-07-12 21:12:23.450117 2024-07-12 2024-07-12 2024-07-12 23:00:00 24 48 FALSE FALSE FALSE FALSE NaN NaN NaN